﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Xeml.Document;
using System.Data.SqlClient;
using XemlUtilities.DataSet;
using System.Threading.Tasks;
using System.Data;
using System.Data.Common;
using System.Globalization;
using Mpi.Common.Collections;
using MySql.Data;
using Mpi.Common;
using MySql.Data.MySqlClient;
using Xeml.Sampling.Contracts;
using System.Diagnostics;
using System.Collections;

namespace XemlUtilities.DataSet
{
    class MySQLXemlDataSet : XemlDataSet
    {
        public MySQLXemlDataSet(XemlDocument xemlDocument, IDataProvider dataProvider)
            : base(xemlDocument, dataProvider)
        {
            Document.Insert = DocumentInsertMethod; // attach the right insert function
            Document.Update = DocumentUpdateMethod; // attach the right insert function
        }

        public MySQLXemlDataSet(IDataProvider dataProvider)
            : base(dataProvider)
        {
            Document.Insert = DocumentInsertMethod; // attach the right insert function
            Document.Update = DocumentUpdateMethod; // attach the right insert function
        }

        public override bool ExperimenterExists(string email, IDbConnection connection, IDbTransaction transaction)
        {
            using (MySqlCommand cmd = (MySqlCommand)connection.CreateCommand())
            {
                cmd.CommandText = string.Concat("SELECT count(*) as cnt FROM ", xemlSchemaName, @".Experimenter WHERE Email = @Email;");
                cmd.CommandType = System.Data.CommandType.Text;

                cmd.Parameters.AddWithValue("@Email", email);
                cmd.Parameters["@Email"].Direction = ParameterDirection.Input;

                cmd.Transaction = (MySqlTransaction)transaction;
                using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                {
                    try
                    {
                        reader.Read();
                        if (!reader.HasRows)
                        {
                            return false;
                        }

                        int count = reader.GetInt32("cnt");
                        return count > 0;
                    }
                    finally
                    {
                        reader.Close();
                    }
                }
            }
        }

        public override void BulkCopyTable(DataTable dataTable, IDbConnection con, IDbTransaction trans, bool throwOnDuplicate = true)
        {
            if (dataTable.Rows.Count > 0)
            {
                using (MySqlCommand cmd = new MySqlCommand(String.Empty, (MySqlConnection)con, (MySqlTransaction)trans))
                {
                    try
                    {
                        // TODO set a limit on how many value-pair one can add and output the query in chunks
                        List<string> columnNames = dataTable.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToList<string>();
                        StringBuilder queryBuilder = new StringBuilder(String.Concat("INSERT INTO `", dataTable.TableName, "` (", String.Join(", ", columnNames), ") VALUES "));
                        String ColumnNamesParameters = String.Concat("(@_{0}_", String.Join(", @_{0}_", columnNames), ")");
                        int i = 0;
                        foreach (DataRow row in dataTable.Rows)
                        {
                            foreach (DataColumn column in dataTable.Columns)
                            {
                                cmd.Parameters.AddWithValue(String.Format("@_{0}_{1}", i, column.ColumnName), row[column]);
                            }
                            i++;
                        }
                        String placeholders = String.Join(", ", GetValuesPlaceHolders(ColumnNamesParameters, --i));
                        queryBuilder.Append(placeholders);
                        cmd.CommandText = queryBuilder.ToString();

                        cmd.ExecuteNonQuery();
                    }
                    catch (MySqlException myex)
                    {
                        if (!throwOnDuplicate && myex.Number == 1022) // Cannot insert duplicate key row in object error
                        {
                            throw new Exception(string.Format("Duplicated key in {0}", dataTable.TableName), myex);
                        }
                        throw new Exception(string.Format("MySqlException in {0}", dataTable.TableName), myex);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(string.Format("Exception in {0}", dataTable.TableName), ex);
                    }
                }
            }
        }

        /// <summary>
        /// Creates a yieldable array from a template 
        /// </summary>
        /// <param name="template"></param>
        /// <param name="max">the maximum number to be formatted into the template param</param>
        /// <returns></returns>
        private IEnumerable<String> GetValuesPlaceHolders(String template, int max)
        {
            for (int i = 0; i <= max; i++)
            {
                yield return String.Format(template, i);
            }
        }

        // has not been tested!
        protected override IDbCommand GetFillCommand(Guid XemlId, IDbConnection connection)
        {
            MySqlCommand cmd = ((MySqlConnection)connection).CreateCommand();
            cmd.CommandText = string.Concat(xemlSchemaName, ".GetDataSet");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@XemlId", XemlId);
            return cmd;
        }

        public override void Fill(Guid XemlId)
        {
            using (MySqlConnection con = (MySqlConnection)_dataProvider.BuildConnection())
            using (MySqlCommand cmd = (MySqlCommand)GetFillCommand(XemlId, con))
            {
                try
                {
                    cmd.Connection.Open();
                    using (cmd.Transaction = cmd.Connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
                    using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SequentialAccess))
                    {
                        Fill(reader);
                    }
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    cmd.Connection.Close();
                }
            }
        }

        #region DataTable specifics
        protected short DocumentInsertMethod(DocumentRow row, IDbConnection connection, IDbTransaction trans)
        {
            using (MySqlCommand cmd = (MySqlCommand)connection.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = string.Concat(@"INSERT INTO ", xemlSchemaName, @".Document
                        (XemlId,Name,StartDate,ObservationTime,Description,ExperimenterEmail,ExperimenterFirstName,ExperimenterLastName,Organisation,NameSpace,XemlVersion,Xeml)
                        VALUES
                        (@XemlId,@Name,@StartDate,@ObservationTime,@Description,@ExperimenterEmail,@ExperimenterFirstName,@ExperimenterLastName,@Organisation,@NameSpace,@XemlVersion,@Xeml);");
                cmd.Transaction = (MySqlTransaction)trans;
                try
                {
                    cmd.Parameters.AddWithValue("@XemlId", row.XemlId);
                    cmd.Parameters.Add("@ExperimenterEmail", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255);
                    cmd.Parameters.Add("@ExperimenterFirstName", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255);
                    cmd.Parameters.Add("@ExperimenterLastName", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255);
                    cmd.Parameters.Add("@Organisation", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255);

                    if (row.IsExperimenterEmailNull())
                    {
                        cmd.Parameters["@ExperimenterEmail"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@ExperimenterEmail"].Value = row.ExperimenterEmail;
                    }
                    if (row.IsExperimenterFirstNameNull())
                    {
                        cmd.Parameters["@ExperimenterFirstName"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@ExperimenterFirstName"].Value = row.ExperimenterFirstName;
                    }
                    if (row.IsExperimenterLastNameNull())
                    {
                        cmd.Parameters["@ExperimenterLastName"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@ExperimenterLastName"].Value = row.ExperimenterLastName;
                    }
                    if (row.IsOrganisationNull())
                    {
                        cmd.Parameters["@Organisation"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@Organisation"].Value = row.Organisation;
                    }

                    cmd.Parameters.AddWithValue("@Name", row.Name);
                    cmd.Parameters.AddWithValue("@StartDate", row.StartDate);
                    cmd.Parameters.AddWithValue("@ObservationTime", row.ObservationTime);
                    cmd.Parameters.AddWithValue("@Description", row.Description);
                    cmd.Parameters.AddWithValue("@NameSpace", XemlDocument.xmlNameSpace);
                    cmd.Parameters.AddWithValue("@XemlVersion", XemlDocument.XemlVersion);
                    cmd.Parameters.AddWithValue("@Xeml", row.Xeml);

                    foreach (MySqlParameter item in cmd.Parameters)
                    {
                        item.Direction = System.Data.ParameterDirection.Input;
                    }

                    MySqlParameter idParam = cmd.CreateParameter();
                    idParam.ParameterName = "@Id";
                    idParam.Direction = System.Data.ParameterDirection.Output;
                    idParam.MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.UInt16;
                    cmd.Parameters.Add(idParam);

                    Debug.WriteLine("attempt to insert into Document table");
                    cmd.ExecuteNonQuery();
                    return Convert.ToInt16(cmd.LastInsertedId);
                }
                catch (MySqlException ex)
                {
                    if (ex.Number == 1022) // Cannot insert duplicate key row in object error
                    {
                        throw new Exception(string.Format("The XEML store does not allow duplicated document names in table {0}.Document! There is already an experiment with the name '{1}'. Change the experiment name or delete the old experiment from the XEML store before reattempting to upload this experiment.", xemlSchemaName, row.Name), ex);
                    }
                    throw new Exception("SqlException in Document.Insert()", ex);
                }
                catch (Exception ex)
                {
                    throw new Exception("Exception in Document.Insert()", ex);
                }
                finally
                {
                }
            }
        }

        protected void DocumentUpdateMethod(DocumentRow row, IDbConnection connection, IDbTransaction trans, short internalId)
        {
            using (MySqlCommand cmd = (MySqlCommand)connection.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = string.Concat("Update ", xemlSchemaName, @".`Document` Set
                            `XemlId` = @XemlId,
                            `Name` = @Name,
                            `StartDate` = @StartDate,
                            `ObservationTime` = @ObservationTime,
                            `Description` = @Description,
                            ExperimenterEmail = @ExperimenterEmail,
                            ExperimenterFirstName = @ExperimenterFirstName,
                            ExperimenterLastName = @ExperimenterLastName,
                            Organisation = @Organisation,
                            `NameSpace` = @NameSpace,
                            `XemlVersion` =@XemlVersion,
                            `Xeml` = @Xeml,
                            `DATE` = NOW(),
                            `USER_NAME` = USER() WHERE `id` = @id;");
                cmd.Transaction = (MySqlTransaction)trans;
                try
                {
                    cmd.Parameters.AddWithValue("@id", internalId);
                    cmd.Parameters.AddWithValue("@XemlId", row.XemlId);

                    cmd.Parameters.Add("@ExperimenterEmail", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255);
                    cmd.Parameters.Add("@ExperimenterFirstName", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255);
                    cmd.Parameters.Add("@ExperimenterLastName", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255);
                    cmd.Parameters.Add("@Organisation", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255);

                    if (row.IsExperimenterEmailNull())
                    {
                        cmd.Parameters["@ExperimenterEmail"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@ExperimenterEmail"].Value = row.ExperimenterEmail;
                    }
                    if (row.IsExperimenterFirstNameNull())
                    {
                        cmd.Parameters["@ExperimenterFirstName"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@ExperimenterFirstName"].Value = row.ExperimenterFirstName;
                    }
                    if (row.IsExperimenterLastNameNull())
                    {
                        cmd.Parameters["@ExperimenterLastName"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@ExperimenterLastName"].Value = row.ExperimenterLastName;
                    }
                    if (row.IsOrganisationNull())
                    {
                        cmd.Parameters["@Organisation"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@Organisation"].Value = row.Organisation;
                    }

                    cmd.Parameters.AddWithValue("@Name", row.Name);
                    cmd.Parameters.AddWithValue("@StartDate", row.StartDate);
                    cmd.Parameters.AddWithValue("@ObservationTime", row.ObservationTime);
                    if (row.IsDescriptionNull())
                    {
                        cmd.Parameters.AddWithValue("@Description", DBNull.Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@Description", row.Description);
                    }
                    cmd.Parameters.AddWithValue("@NameSpace", XemlDocument.xmlNameSpace);
                    cmd.Parameters.AddWithValue("@XemlVersion", XemlDocument.XemlVersion);
                    cmd.Parameters.AddWithValue("@Xeml", row.Xeml);

                    foreach (MySqlParameter item in cmd.Parameters)
                    {
                        item.Direction = System.Data.ParameterDirection.Input;
                    }

                    Debug.WriteLine("attempt to update into Document table");
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception("Exception in Document.Update()", ex);
                }
                finally
                {
                }
            }
        }

        #endregion DataTable specifics

        #region SQL specifics
        public override string GetUserNameFunction()
        {
            return "USER()";
        }
        #endregion SQL specifics
    }
}